CREATE TABLE [dbo].[PciAuditLog]
(
[PciAuditLogId] [int] NOT NULL IDENTITY(1, 1),
[SqlLogin] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_PciAuditLog_SqlLogin] DEFAULT (suser_sname()),
[UserId] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[UserKey] [uniqueidentifier] NULL,
[EventType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EventTimestamp] [datetime] NOT NULL CONSTRAINT [DF_PciAuditLog_EventTimestamp] DEFAULT (getdate()),
[Result] [bit] NULL,
[OriginationCode] [int] NULL,
[CC_LAST4] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ENCRYPT_CC_NUMBER] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

GO
--add delete trigger to PciAuditLog table
CREATE TRIGGER [dbo].[asi_PciAuditLog_Delete]
ON [dbo].[PciAuditLog]
INSTEAD OF DELETE
AS
    INSERT INTO PciAuditLog ([EventType], [Result], [OriginationCode]) VALUES ('DeleteLog',0,100)

GO
--add insert trigger to PciAuditLog table
CREATE TRIGGER [dbo].[asi_PciAuditLog_Insert]
ON [dbo].[PciAuditLog]
INSTEAD OF INSERT
AS
BEGIN
      INSERT INTO [dbo].[PciAuditLog]
      ([UserId],[UserKey],[EventType],[Result],[OriginationCode],[CC_LAST4],[ENCRYPT_CC_NUMBER])
      SELECT [UserId],[UserKey],[EventType],[Result],[OriginationCode],[CC_LAST4],[ENCRYPT_CC_NUMBER]
      FROM inserted i
      WHERE NOT EXISTS(SELECT 1 FROM [dbo].[PciAuditLog] t WHERE i.[SqlLogin]=t.[SqlLogin]
                        AND isnull(i.[UserId],'')=isnull(t.[UserId],'')  
                        AND ((i.[UserKey]=t.[UserKey]) OR (i.[UserKey] is null AND t.[UserKey] is null))
                        AND i.[EventType]=t.[EventType]
                        AND (i.[EventTimestamp]-0.0007)<t.[EventTimestamp]
                        AND i.[Result]=t.[Result]
                        AND i.[OriginationCode]=t.[OriginationCode]
                        AND i.[CC_LAST4]=t.[CC_LAST4]
                        AND i.[ENCRYPT_CC_NUMBER]=t.[ENCRYPT_CC_NUMBER])
END

GO
--add update trigger to PciAuditLog table
CREATE TRIGGER [dbo].[asi_PciAuditLog_Update]
ON [dbo].[PciAuditLog]
INSTEAD OF UPDATE
AS
    INSERT INTO PciAuditLog ([EventType], [Result], [OriginationCode]) VALUES ('UpdateLog',0,100)

GO
ALTER TABLE [dbo].[PciAuditLog] ADD CONSTRAINT [PK_PciAuditLog] PRIMARY KEY CLUSTERED ([PciAuditLogId]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_PciAuditLog] ON [dbo].[PciAuditLog] ([EventTimestamp]) ON [PRIMARY]
GO
GRANT SELECT ON  [dbo].[PciAuditLog] TO [public]
GO